﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

/**
 *  Author ：龚秋志
 *  StartTime : 2021/12/13
 *  EndTime : 2022/3/16   
 *  Desc: 注意 仅作用于同步代码，异步调用会产生线程安全问题！！！
 */
public static class DBHelper
{
    #region 私有方法
    /// <summary>
    /// 判断是否为基础数据类型
    /// </summary>
    /// <param name="t"></param>
    /// <returns></returns>
    private static bool isBaseDataType(Type t)
    {
        return (t == typeof(string)
         || t == typeof(Int32)
         || t == typeof(double)
         || t == typeof(float)
         || t == typeof(decimal)
         || t == typeof(Int16)
         || t == typeof(Int64)
         || t == typeof(Int16?)
         || t == typeof(Int32?)
         || t == typeof(Int64?)
         || t == typeof(byte?)
         || t == typeof(double?)
         || t == typeof(float?)
         || t == typeof(decimal?)
         );
    }

    /// <summary>
    /// 打开数据库连接
    /// </summary>
    private static void connOpen()
    {
        try
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            else if (conn.State == ConnectionState.Broken)
            {
                conn.Close();
                conn.Open();
            }
            else if (conn.State == ConnectionState.Connecting)
            {
                throw new Exception("连接对象正在与数据源连接！");
            }
            else if (conn.State == ConnectionState.Executing)
            {
                throw new Exception("连接对象正在执行命令！");
            }
            else if (conn.State == ConnectionState.Fetching)
            {
                throw new Exception("连接对象正在检索数据！");
            }
        }
        catch (Exception ex)
        {
            throw new Exception($"打开数据库连接出现异常！SqlConnection连接状态为：{conn.State}  \n  错误信息： \n {ex}");
        }

    }
    #endregion

    private static string StrConn = TeacherHelp.Public.ConfigHelper.strConn;

    private static SqlConnection conn = new SqlConnection(StrConn);

    /// <summary>
    /// 获取数据集合
    /// </summary>
    /// <param name="sql">sql语句</param>
    /// <returns></returns>
    public static DataTable GetDataTable(string sql)
    {
        DataTable table = new DataTable();
        try
        {
            connOpen();
            SqlDataAdapter dap = new SqlDataAdapter(sql, conn);
            dap.Fill(table);
        }
        catch (Exception ex)
        {
            throw new Exception($"\n DBHelper.GetDataTable  发生异常！sql : \n {sql} \n   错误信息:{ex}");
        }
        finally
        {
            conn.Close();
        }
        return table;
    }

    /// <summary>
    /// 受影响行数
    /// </summary>
    /// <param name="sql">sql</param>
    /// <returns></returns>
    public static int ExecuteNonQuery(string sql)
    {

        int result = -1;
        try
        {
            connOpen();
            SqlCommand comm = new SqlCommand(sql, conn);
            result = comm.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            throw new Exception($"\n  DBHelper.ExecuteNonQuery  发生异常！sql : \n {sql} \n   错误信息:{ex}");
        }
        finally
        {
            conn.Close();
        }
        return result;
    }

    /// <summary>
    /// 首行首列
    /// </summary>
    /// <param name="sql">sql语句</param>
    /// <returns></returns>
    public static object ExecuteScalar(string sql)
    {
        object obj = null;
        try
        {
            connOpen();
            SqlCommand comm = new SqlCommand(sql, conn);
            obj = comm.ExecuteScalar();
        }
        catch (Exception ex)
        {
            throw new Exception($"\n   DBHelper.ExecuteScalar  发生异常！sql : \n {sql} \n   错误信息:{ex}");
        }
        finally
        {
            conn.Close();
        }
        return obj;
    }

    /// <summary>
    /// 执行事务
    /// </summary>
    /// <param name="sql">sql语句</param>
    /// <returns></returns>
    public static int ExecuteTransaction(string sql)
    {
        int result = -1;
        SqlTransaction transaction = null;
        try
        {
            connOpen();
            transaction = conn.BeginTransaction();
            SqlCommand comm = new SqlCommand(sql, conn);
            comm.Transaction = transaction;
            result = comm.ExecuteNonQuery();
            transaction.Commit();
        }
        catch (Exception ex)
        {
            transaction.Rollback();
            result = -1;
            throw new DataException($"\n   DBHelper.ExecuteTransaction  发生异常！sql : \n {sql} \n   错误信息:{ex}");
        }
        finally
        {
            conn.Close();
        }

        return result;
    }

    /// <summary>
    /// 根据SQL语句获取对象
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="sql">sql语句</param>
    /// <returns></returns>
    public static T GetOneModel<T>(string sql)
    {
        DataTable table = GetDataTable(sql);
        if (table.Rows.Count == 0) return default;
        return GetOneModel<T>(table.Rows[0]);
    }

    /// <summary>
    /// 根据DataRow获取对象
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="row"></param>
    /// <returns></returns>
    public static T GetOneModel<T>(DataRow row)
    {
        Type type = typeof(T);
        if (isBaseDataType(type))
        {
            return (T)row[0];
        }
        T model = Activator.CreateInstance<T>();
        foreach (var item in type.GetProperties())
        {
            try
            {
                if (!row.Table.Columns.Contains(item.Name)) continue;

                if (row[item.Name] == DBNull.Value) continue;

                var value = row[item.Name];

                type.GetProperty(item.Name).SetValue(model, value);
            }
            catch (Exception ex)
            {
                throw new Exception($"\n   DBHelper.GetOne  发生异常！value : \n {item.Name} \n   错误信息:{ex}");
            }
        }
        return model;
    }

    /// <summary>
    /// 根据sql语句获取list集合
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="sql">sql语句</param>
    /// <returns></returns>
    public static List<T> GetListModel<T>(string sql)
    {
        List<T> list = new List<T>();
        DataTable table = GetDataTable(sql);
        foreach (DataRow rowItem in table.Rows)
        {
            list.Add(GetOneModel<T>(rowItem));
        }
        return list;
    }

}

